/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac4.bbdd;

import edu.uoc.tdp.pac4.beans.Client;
import edu.uoc.tdp.pac4.beans.ClientVehicle;
import edu.uoc.tdp.pac4.beans.Peca;
import edu.uoc.tdp.pac4.beans.Reparacio;
import edu.uoc.tdp.pac4.beans.Solicitud;
import edu.uoc.tdp.pac4.beans.StockPeca;
import edu.uoc.tdp.pac4.beans.Vehicle;
import edu.uoc.tdp.pac4.comun.Enumerados;
import edu.uoc.tdp.pac4.comun.PAC4Exception;
import edu.uoc.tdp.pac4.i18n.TDSLanguageUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.rmi.RemoteException;
import java.sql.Connection;
//import java.sql.Date;
import java.util.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Properties;

/**
 *
 * @author mirica
 */
public class GestorAdministrativaBBDD {
    
    private static final long serialVersionUID = 1L;
    public Connection conexion;
    final String propertiesFile = new File("").getAbsolutePath() + "//src//properties///configuration.properties";
    final String driver = "org.postgresql.Driver";

    public GestorAdministrativaBBDD() throws PAC4Exception {
        conectaBD();
    }
    
    private void conectaBD() throws PAC4Exception {
        try {
            Class.forName("org.postgresql.Driver");
            Properties localProperties = new Properties();
            localProperties.load(new FileInputStream(this.propertiesFile));
            String str1 = localProperties.getProperty("url");
            String str2 = localProperties.getProperty("username");
            String str3 = localProperties.getProperty("password");
            this.conexion = DriverManager.getConnection(str1, str2, str3);
        } catch (ClassNotFoundException localClassNotFoundException) {
            //Servidor.diplay(localClassNotFoundException.getMessage());
            throw new PAC4Exception(TDSLanguageUtils.getMessage("Aplication.JDBCKO"));
        } catch (FileNotFoundException localFileNotFoundException) {
            // Servidor.diplay(localFileNotFoundException.getMessage());
            throw new PAC4Exception(TDSLanguageUtils.getMessage("Aplication.ConfigurationKO"));
        } catch (IOException localIOException) {
            // Servidor.diplay(localIOException.getMessage());
            throw new PAC4Exception(TDSLanguageUtils.getMessage("Aplication.PropertiesKO"));
        } catch (SQLException localSQLException) {
            // Servidor.diplay(localSQLException.getMessage());
            throw new PAC4Exception(TDSLanguageUtils.getMessage("Aplication.BBDDKO"));
        } catch (Exception localException) {
           // Servidor.diplay(localException.getMessage());
            throw new PAC4Exception(localException.getMessage());
        }
    }
   
    public ClientVehicle getCliente(String nif) throws PAC4Exception {
        ClientVehicle cv = null;
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT \"Client\".nom, \"Client\".cognoms, \"Client\".\"adreça\", \"Client\".nif, \"Client\".poblacio, \"Client\".\"codiPostal\", \"Client\".\"numClient\", \"Client\".\"dataAlta\", " +
                        "\"Vehicle\".marca, \"Vehicle\".tipus, \"Vehicle\".num_chasis, \"Vehicle\".model, \"Vehicle\".matricula, \"Vehicle\".color, \"Vehicle\".\"any\", \"Vehicle\".\"numReparacio\", \"Vehicle\".\"IdVehicle\" " +
                        "FROM \"Client\" LEFT JOIN \"Vehicle\" ON \"Client\".\"IdVehicle\" = \"Vehicle\".\"IdVehicle\" " +
                        "WHERE \"Client\".nif = '" + nif + "'";
            
            result = st.executeQuery(str);
            
            if (result.next()) {
                cv = new ClientVehicle();
                cv.setAdresa(result.getString("adreça"));
                cv.setCodiPostal(result.getInt("codiPostal"));
                cv.setCognoms(result.getString("cognoms"));
                cv.setDataAlta(result.getDate("dataAlta"));
                cv.setNif(result.getString("nif"));
                cv.setNom(result.getString("nom"));
                cv.setNumCliente(result.getInt("numClient"));
                cv.setNum_chasis(result.getString("num_chasis"));
                cv.setPoblacio(result.getString("poblacio"));
                
                cv.setIdVehicle(result.getInt("IdVehicle"));
                cv.setMarca(result.getString("marca"));
                cv.setTipus(result.getString("tipus"));
                cv.setModel(result.getString("model"));
                cv.setMatricula(result.getString("matricula"));
                cv.setColor(result.getString("color"));
                cv.setAnio(result.getDate("any"));
                cv.setNumReparacio(result.getInt("numReparacio"));
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return cv;
    }
    
    public void updateClient (Client client) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            String query = "UPDATE \"Client\" " 
                    + "SET nom=?, cognoms=?, \"adreça\"=?, \"codiPostal\"=? " 
                    + "WHERE nif = '" + client.getNif() + "'";
            
            st = conexion.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            st.setString(1, client.getNom());
            st.setString(2, client.getCognoms());
            st.setString(3, client.getAdresa());
            st.setInt(4, client.getCodiPostal());
            st.execute();
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }
    
    public void updateVehicle (Vehicle vehicle) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            String query = "UPDATE \"Vehicle\" " +
                    " SET marca=?, num_chasis=?, model=?, matricula=? " +
                    " WHERE \"IdVehicle\" = " + vehicle.getIdVehicle();
            
            st = conexion.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            st.setString(1, vehicle.getMarca());
            st.setString(2, vehicle.getNum_chasis());
            st.setString(3, vehicle.getModel());
            st.setString(4, vehicle.getMatricula());
            st.execute();
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }
 
    public int insertClient (Client client) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            int NumCliente = 0;
            
            String query = "INSERT INTO \"Client\"(nom, cognoms, \"adreça\", nif, \"codiPostal\", \"dataAlta\", \"IdVehicle\") " +
                    " VALUES (?, ?, ?, ?, ?, CURRENT_DATE, ?);";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);        
            st.setString(1, client.getNom()); 
            st.setString(2, client.getCognoms());                            
            st.setString(3, client.getAdresa());
            st.setString(4, client.getNif());
            st.setInt(5, client.getCodiPostal());
            st.setInt(6, client.getIdVehicle());
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            if (rs.next())
                NumCliente = rs.getInt(8);
            
            return NumCliente;
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }       
        
    public int insertVehicle (Vehicle vehicle) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            int IdVehiculo = 0;
            
            String query = "INSERT INTO \"Vehicle\"(marca, tipus, num_chasis, model, matricula, color, \"any\", \"numReparacio\") " +
                    " VALUES (?, null, ?, ?, ?, null, null, null);";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setString(1, vehicle.getMarca()); 
            st.setString(2, vehicle.getNum_chasis()); 
            st.setString(3, vehicle.getModel());
            st.setString(4, vehicle.getMatricula());
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            if (rs.next())
                IdVehiculo = rs.getInt(9);
            
            return IdVehiculo;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }        
    }
    
    public Solicitud getSolicitud (int numSolicitud) throws PAC4Exception {
        Solicitud s = null;
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT \"numSol\", comentaris, \"dataAlta\", \"dataFinalitzacio\", client, \"IdSolicitudEstadoTipo\", asseguradora, \"numPoliza\" " +
                    " FROM \"Solicitud\" " +
                    " WHERE \"numSol\" = '" + numSolicitud + "';";
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            if (result.next()) {
                s = new Solicitud();
                s.setAsseguradora(result.getString("asseguradora"));
                s.setClient(result.getString("client"));
                s.setComentaris(result.getString("comentaris"));
                s.setDataAlta(result.getDate("dataAlta"));
                s.setDataFinalitzacio(result.getDate("dataFinalitzacio"));
                s.setNumPoliza(result.getString("numPoliza"));
                s.setNumSol(result.getInt("numSol"));
                s.setIdSolicitudEstadoTipo(result.getInt("IdSolicitudEstadoTipo"));
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return s;        
    }
        
    public int insertSolicitud (Solicitud solicitud) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            int numSol = 0;
            
            String query = "INSERT INTO \"Solicitud\"(comentaris, \"dataAlta\", client, \"numPoliza\", asseguradora, \"IdSolicitudEstadoTipo\")\n" +
                    " VALUES (?, CURRENT_DATE, ?, ?, ?, ?);";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);      
            st.setString(1, solicitud.getComentaris()); 
            st.setString(2, solicitud.getClient());
            st.setString(3, solicitud.getNumPoliza());
            st.setString(4, solicitud.getAsseguradora());
            st.setInt(5, solicitud.getIdSolicitudEstadoTipo());
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            if (rs.next())
                numSol = rs.getInt(7);
            
            return numSol;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }
    
    public void updateSolicitud (Solicitud solicitud) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            int IdSolicitudEstadoTipo = solicitud.getIdSolicitudEstadoTipo();
            String query = "";
            
            if(IdSolicitudEstadoTipo == Enumerados.SolicitudEstadoTipo.FINALIZADA.getEstado()) {
                query = "UPDATE \"Solicitud\" " +
                    " SET comentaris=?, \"dataFinalitzacio\"=CURRENT_DATE, \"numPoliza\"=?, asseguradora=?, \"IdSolicitudEstadoTipo\"=? " +
                    " WHERE \"numSol\"=" + solicitud.getNumSol();
            }
            else {
                query = "UPDATE \"Solicitud\" " +
                    " SET comentaris=?, \"numPoliza\"=?, asseguradora=?, \"IdSolicitudEstadoTipo\"=? " +
                    " WHERE \"numSol\"=" + solicitud.getNumSol();
            }
            
            st = conexion.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            st.setString(1, solicitud.getComentaris());
            st.setString(2, solicitud.getNumPoliza());
            st.setString(3, solicitud.getAsseguradora());
            st.setInt(4, solicitud.getIdSolicitudEstadoTipo());
            st.execute();
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } catch (Exception e) {
            throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));    
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }
    
    public StockPeca getStockPeca (int codiPeca, int idTaller) throws PAC4Exception {
        StockPeca sp = null;
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT \"IdStockPeca\", \"CodiPeca\", \"Stock\", \"IdTaller\", \"StockMinim\"\n" +
                    "  FROM \"StockPeca\" " +
                    " WHERE \"CodiPeca\" = '" + codiPeca + "'" +
                    " AND \"IdTaller\" =  " + idTaller + ";";
            
            result = st.executeQuery(str);

            if (result.next()) {
                sp = new StockPeca();
                sp.setCodiPeca(result.getInt("CodiPeca"));
                sp.setIdStockPeca(result.getInt("IdStockPeca"));
                sp.setIdTaller(result.getInt("IdTaller"));
                sp.setStock(result.getInt("Stock"));
                sp.setStockMinim(result.getInt("StockMinim"));
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return sp;
    }
    
    public ArrayList<Peca> getPecas () throws PAC4Exception {
        ArrayList<Peca> lista = new ArrayList<Peca>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT \"CodiPeca\", \"Descripcio\", \"PVP\", \"PVD\", \"Marca\", \"Model\", \"IdProveidor\"\n" +
                    "  FROM \"Peca\";";
                    
            result = st.executeQuery(str);
            
            while(result.next()) {
                Peca p = new Peca();
                p.setCodiPeca(result.getInt("CodiPeca"));
                p.setDescripcio(result.getString("Descripcio"));
                p.setIdProveidor(result.getInt("IdProveidor"));
                p.setMarca(result.getString("Marca"));
                p.setModel(result.getString("Model"));
                p.setPvd(result.getFloat("PVD"));
                p.setPvp(result.getFloat("PVP"));
                lista.add(p);
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return lista;        
    }
 
    public void updateStockPeca (StockPeca stockPeca) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            String query= "UPDATE \"StockPeca\"\n" +
                       " SET \"Stock\"=?\n" +
                       " WHERE \"IdStockPeca\"=" + stockPeca.getIdStockPeca();
            
            st = conexion.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            st.setInt(1, stockPeca.getStock());
            st.execute();
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } catch (Exception e) {
            throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));    
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }
    
    public void insertStockPeca (StockPeca stockPeca) throws PAC4Exception {
        PreparedStatement st = null;
        try {
            String query = "INSERT INTO \"StockPeca\"(\"CodiPeca\", \"Stock\", \"IdTaller\", \"StockMinim\")\n" +
                    " VALUES (?, ?, ?, null);";
            
            st = conexion.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);         
            st.setInt(1, stockPeca.getCodiPeca()); 
            st.setInt(2, stockPeca.getStock());
            st.setInt(3, stockPeca.getIdTaller());
            st.execute();
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new PAC4Exception(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new PAC4Exception(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }

}

